﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class LichBay_DAO
    {
        //       create table LICHBAY
        //(
        //MaLB varchar(4) not null,
        //MaCB varchar(9),
        //NgayDi datetime,
        //NgayDen datetime,

        //primary key (MaLB)
        public static List<LichBay_DTO> layDanhSachLichBay()
        {
            string query = "select * from Lichbay";

            List<LichBay_DTO> list = new List<LichBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                LichBay_DTO lb = new LichBay_DTO();
                lb.maCB = dr["MaCB"].ToString();
                lb.maLB = dr["MaLB"].ToString();
                lb.ngayDen = DateTime.Parse(dr["NgayDen"].ToString());
                lb.ngayDi = DateTime.Parse(dr["NgayDi"].ToString());

                list.Add(lb);
            }
            return list;
        }
        public static LichBay_DTO layLichBayTheoMaLichBay(String maLB)
        {
            string query = "Select * from LichBay where maLB = '" + maLB + "'";
            DataTable lb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            LichBay_DTO lb = new LichBay_DTO();
            lb.maCB = lb_tab.Select()[0]["maCB"].ToString();
            lb.maLB = lb_tab.Select()[0]["maLB"].ToString();
            lb.ngayDen = DateTime.Parse(lb_tab.Select()[0]["NgayDen"].ToString());
            lb.ngayDi = DateTime.Parse(lb_tab.Select()[0]["NgayDi"].ToString());
            return lb;
        }

        public static List<LichBay_DTO> layLichBayTheoNgayDi(DateTime ngayDi)
        {
            string query = "select * from lichbay where ngaydi = '" + ngayDi + "'";
          
            List<LichBay_DTO> list = new List<LichBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            foreach (DataRow dr in dt.Rows)
            {
                LichBay_DTO lb = new LichBay_DTO();
                lb.maCB = dr["MaCB"].ToString();
                lb.maLB = dr["MaLB"].ToString();
                lb.ngayDen = DateTime.Parse(dr["NgayDen"].ToString());
                lb.ngayDi = DateTime.Parse(dr["NgayDi"].ToString());

                list.Add(lb);
            }
            return list;
        }

        public static List<LichBay_DTO> layLichBayTheoNgayDen(DateTime ngayDen)
        {
            string query = "select * from lichbay where ngayden = '" + ngayDen + "'";

            List<LichBay_DTO> list = new List<LichBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            foreach (DataRow dr in dt.Rows)
            {
                LichBay_DTO lb = new LichBay_DTO();
                lb.maCB = dr["MaCB"].ToString();
                lb.maLB = dr["MaLB"].ToString();
                lb.ngayDen = DateTime.Parse(dr["NgayDen"].ToString());
                lb.ngayDi = DateTime.Parse(dr["NgayDi"].ToString());

                list.Add(lb);
            }
            return list;
        }
        
        public static List<CT_ChuyenBay_LichBay_DTO> layThongKeLichBay(string maLB)
        {
            string query = "select cb.MaCB, lb.MaLB, cb.GioDen, cb.GioDi" +
                " from  chuyenbay cb, lichbay lb" +
                " where cb.MaCB = lb.MaLB and lb.MaLB = '" + maLB + "'";
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<CT_ChuyenBay_LichBay_DTO> list = new List<CT_ChuyenBay_LichBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                CT_ChuyenBay_LichBay_DTO cb = new CT_ChuyenBay_LichBay_DTO();
                cb.maCB = dr["MaCB"].ToString();
                cb.maLB = dr["MaLB"].ToString();
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();

                SanBay_DTO sb = SanBay_DAO.laySanBayDenTheoMaCB(cb.maCB);
                cb.sbDen = sb.tinh;

                sb = SanBay_DAO.laySanBayDiTheoMaCB(cb.maCB);
                cb.sbDi = sb.tinh;
                cb.soGheDat = Ve_DAO.soGheDat1(cb.maLB);
                cb.soGheTrong = Ve_DAO.soGheTrongHang1(cb.maLB) + Ve_DAO.soGheTrongHang2(cb.maLB);

                list.Add(cb);
            }
            return list;
        }


        public static List<CT_ChuyenBay_LichBay_DTO> traCuuChuyenBay(string noiDi, string noiDen, DateTime ngayDi)
        {
            string query = "select lb.MaLB, cb.MaCB, cb.GioDen, cb.Giodi " +
                    "from lichbay lb, (select cb1.MaCB, cb1.giodi, cb1.gioden " +
                     "from chuyenbay cb1 , chitietsb ctsb, sanbay sb" +
                    "where cb1.Macb = ctsb.macb and ctsb.masb = sb.masb and ((sb.tinh = '" + noiDi + "' and ctsb.loai = 1) or (sb.tinh = '" + noiDen + "' and ctsb.loai = 2)) " +
                    "group by cb1.MaCB, cb1.giodi, cb1.gioden " +
                    "having count(cb1.Macb) = 2	) cb " +
                    "where lb.MaCB = cb.macb and lb.NgayDi = '2/3/2011' ";


            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<CT_ChuyenBay_LichBay_DTO> list = new List<CT_ChuyenBay_LichBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                CT_ChuyenBay_LichBay_DTO cb = new CT_ChuyenBay_LichBay_DTO();
                cb.maCB = dr["MaCB"].ToString();
                cb.maLB = dr["MaLB"].ToString();
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();

                SanBay_DTO sb = SanBay_DAO.laySanBayDenTheoMaCB(cb.maCB);
                cb.sbDen = sb.tinh;

                sb = SanBay_DAO.laySanBayDiTheoMaCB(cb.maCB);
                cb.sbDi = sb.tinh;
                cb.soGheDat = Ve_DAO.soGheDat1(cb.maLB);
                cb.soGheTrong = Ve_DAO.soGheTrongHang1(cb.maLB) + Ve_DAO.soGheTrongHang2(cb.maLB);

                list.Add(cb);
            }
            return list;
        }

        //Cac ham tuong tac voi co so du lieu
        //       create table LICHBAY
        //(
        //MaLB varchar(4) not null,
        //MaCB varchar(9),
        //NgayDi datetime,
        //NgayDen datetime,
        public static bool themLichBay(LichBay_DTO lichBay)  // them lich bay
        {
            string query = "insert into lichbay (MaLB, MaCB, NgayDi, NgayDen) " +
                "values ('" + lichBay.maLB + "', '" + lichBay.maCB + "', '" + lichBay.ngayDi + "', '" + lichBay.ngayDen + "')";
            int n = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (n == 1)
                return true;
            return false;
        }

        public static bool ktTonTaiLichBay(string MaLB)
        {
            string query = "select * from lichbay where MaLB = '" + MaLB + "'";

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            if (dt.Rows.Count == 1)
                return true;
            return false;
        }
        public static bool xoaLichBay(string MaLB)    // xoa lich bay
        {
            string query0 = "delete ve where malb = '" + MaLB + "'";

            if (SqlDataAccessHelper.ExecuteNoneQuery(query0) == -1)
                return false;

            string query = "delete lichbay where MalB = '" + MaLB + "'";

            if (SqlDataAccessHelper.ExecuteNoneQuery(query) == -1)
                return false;

            return true;
        }
        public static bool suaLichBay(LichBay_DTO lichBay)   //Sua lich bay
        {
            string query = "update lichbay set  MaCB = '" + lichBay.maCB + "', NgayDen = '" + lichBay.ngayDen + "', NgayDi = '" + lichBay.ngayDi + "'"
                + " where MaLB = '" + lichBay.maLB + "'";

            if (SqlDataAccessHelper.ExecuteNoneQuery(query) == -1)
                return false;

            return true;
        }
    }
}
